# database.py - 完整功能版本
import sqlite3
import json
from datetime import datetime
from models import User, UserRole, Question, QuestionType, DifficultyLevel, Paper
from typing import List, Dict, Optional, Any
import uuid


class Database:
    def __init__(self, db_path='exam_system.db'):
        self.db_path = db_path
        self.init_database()
        self.ensure_default_data()

    def init_database(self):
        """初始化数据库表结构"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        # 创建用户表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY,
                username TEXT UNIQUE NOT NULL,
                email TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                role TEXT NOT NULL,
                created_time TEXT NOT NULL
            )
        ''')

        # 创建试题表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS questions (
                id TEXT PRIMARY KEY,
                content TEXT NOT NULL,
                question_type TEXT NOT NULL,
                difficulty INTEGER NOT NULL,
                subject TEXT NOT NULL,
                chapter TEXT,
                options TEXT,
                answer TEXT,
                explanation TEXT,
                tags TEXT,
                usage_count INTEGER DEFAULT 0,
                creator_id TEXT,
                created_time TEXT NOT NULL,
                FOREIGN KEY (creator_id) REFERENCES users (id)
            )
        ''')

        # 创建试卷表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS papers (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                description TEXT,
                subject TEXT NOT NULL,
                questions TEXT NOT NULL,
                total_score INTEGER DEFAULT 0,
                creator_id TEXT,
                created_time TEXT NOT NULL,
                FOREIGN KEY (creator_id) REFERENCES users (id)
            )
        ''')

        # 创建操作日志表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS logs (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id TEXT,
                action TEXT NOT NULL,
                description TEXT,
                ip_address TEXT,
                timestamp TEXT NOT NULL,
                FOREIGN KEY (user_id) REFERENCES users (id)
            )
        ''')

        # 创建相似度检查结果表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS similarity_results (
                id TEXT PRIMARY KEY,
                question_id TEXT,
                similar_question_id TEXT,
                similarity_score REAL,
                created_time TEXT NOT NULL
            )
        ''')

        # 创建难度分析结果表
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS difficulty_analysis (
                id TEXT PRIMARY KEY,
                question_id TEXT,
                predicted_difficulty INTEGER,
                actual_difficulty INTEGER,
                confidence REAL,
                created_time TEXT NOT NULL
            )
        ''')

        cursor.execute('''
                CREATE TABLE IF NOT EXISTS exam_records (
                    id TEXT PRIMARY KEY,
                    question_id TEXT NOT NULL,
                    exam_date TEXT NOT NULL,
                    exam_name TEXT,
                    paper_id TEXT,
                    subject TEXT,
                    chapter TEXT,
                    knowledge_points TEXT,
                    created_time TEXT NOT NULL,
                    FOREIGN KEY (question_id) REFERENCES questions (id),
                    FOREIGN KEY (paper_id) REFERENCES papers (id)
                )
            ''')

        # 新增：相似度检查结果表（修改现有的表结构）
        cursor.execute('''
                CREATE TABLE IF NOT EXISTS similarity_results (
                    id TEXT PRIMARY KEY,
                    question_id TEXT NOT NULL,
                    similar_question_id TEXT NOT NULL,
                    similarity_score REAL NOT NULL,
                    algorithm_used TEXT DEFAULT 'jaccard',
                    created_time TEXT NOT NULL,
                    FOREIGN KEY (question_id) REFERENCES questions (id),
                    FOREIGN KEY (similar_question_id) REFERENCES questions (id)
                )
            ''')

        # 新增：难度分析表（修改现有的表结构）
        cursor.execute('''
                CREATE TABLE IF NOT EXISTS difficulty_analysis (
                    id TEXT PRIMARY KEY,
                    question_id TEXT NOT NULL,
                    predicted_difficulty INTEGER,
                    actual_difficulty INTEGER,
                    confidence REAL,
                    analysis_factors TEXT,
                    model_version TEXT DEFAULT 'v1.0',
                    created_time TEXT NOT NULL,
                    FOREIGN KEY (question_id) REFERENCES questions (id)
                )
            ''')

        # 新增：答题统计表
        cursor.execute('''
                CREATE TABLE IF NOT EXISTS answer_statistics (
                    id TEXT PRIMARY KEY,
                    question_id TEXT NOT NULL,
                    total_attempts INTEGER DEFAULT 0,
                    correct_attempts INTEGER DEFAULT 0,
                    average_time REAL DEFAULT 0,
                    last_used TEXT,
                    FOREIGN KEY (question_id) REFERENCES questions (id)
                )
            ''')

        # 新增：系统配置表
        cursor.execute('''
                CREATE TABLE IF NOT EXISTS system_config (
                    key TEXT PRIMARY KEY,
                    value TEXT NOT NULL,
                    description TEXT,
                    updated_time TEXT NOT NULL
                )
            ''')

        conn.commit()
        conn.close()
        print("📚 数据库初始化完成")

    def ensure_default_data(self):
        """确保有默认的学科数据"""
        default_subjects = ['数学', '语文', '英语', '物理', '化学', '生物', '历史', '地理', '政治', '计算机']
        # 这里可以添加默认学科数据的逻辑
        pass

    # ==================== 用户管理方法 ====================
    def create_user(self, user):
        """创建用户 - 修复密码字段处理"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # 智能获取密码字段
            password_value = None
            if hasattr(user, 'password'):
                password_value = user.password
            elif hasattr(user, 'password_hash'):
                password_value = user.password_hash
            elif hasattr(user, 'hashed_password'):
                password_value = user.hashed_password
            else:
                print(f"❌ 用户对象没有密码属性")
                return False

            cursor.execute('''
                INSERT INTO users (id, username, email, password, role, created_time)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (
                user.id, user.username, user.email, password_value,
                user.role.value, user.created_time.isoformat()
            ))

            conn.commit()
            print(f"✅ 用户创建成功: {user.username}")
            return True
        except sqlite3.IntegrityError as e:
            if "UNIQUE constraint failed: users.username" in str(e):
                print(f"⚠️ 用户名已存在: {user.username}")
            elif "UNIQUE constraint failed: users.email" in str(e):
                print(f"⚠️ 邮箱已存在: {user.email}")
            else:
                print(f"❌ 创建用户失败: {e}")
            return False
        except Exception as e:
            print(f"❌ 创建用户失败: {e}")
            return False
        finally:
            conn.close()

    def get_all_users(self):
        """获取所有用户列表 - 修复参数名称"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute(
                'SELECT id, username, email, password, role, created_time FROM users ORDER BY created_time DESC')
            rows = cursor.fetchall()

            users = []
            for row in rows:
                try:
                    user = User(
                        id=row[0],
                        username=row[1],
                        email=row[2],
                        password=row[3],  # 修复：使用 password 而不是 password_hash
                        role=UserRole(row[4]),
                        created_time=datetime.fromisoformat(row[5]) if row[5] else datetime.now()
                    )
                    users.append(user)
                except Exception as e:
                    print(f"❌ 解析用户数据失败: {e}")
                    print(f"📝 用户数据: {row}")
                    continue

            print(f"📋 获取到 {len(users)} 个用户")
            return users
        except Exception as e:
            print(f"❌ 获取用户列表失败: {e}")
            return []
        finally:
            conn.close()

    def get_user_by_username(self, username):
        """根据用户名获取用户 - 修复参数名称"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT * FROM users WHERE username = ?', (username,))
            row = cursor.fetchone()

            if row:
                user = User(
                    id=row[0],
                    username=row[1],
                    email=row[2],
                    password=row[3],  # 修复：使用 password 而不是 password_hash
                    role=UserRole(row[4]),
                    created_time=datetime.fromisoformat(row[5])
                )
                return user
            return None
        except Exception as e:
            print(f"❌ 获取用户失败: {e}")
            return None
        finally:
            conn.close()

    def get_user_by_id(self, user_id):
        """根据用户ID获取用户 - 修复参数名称"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT * FROM users WHERE id = ?', (user_id,))
            row = cursor.fetchone()

            if row:
                user = User(
                    id=row[0],
                    username=row[1],
                    email=row[2],
                    password=row[3],  # 修复：使用 password 而不是 password_hash
                    role=UserRole(row[4]),
                    created_time=datetime.fromisoformat(row[5])
                )
                return user
            return None
        except Exception as e:
            print(f"❌ 获取用户失败: {e}")
            return None
        finally:
            conn.close()

    def update_user(self, user_id, data):
        """更新用户信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            update_fields = []
            params = []

            if 'username' in data:
                update_fields.append('username = ?')
                params.append(data['username'])

            if 'email' in data:
                update_fields.append('email = ?')
                params.append(data['email'])

            if 'password' in data:
                update_fields.append('password = ?')
                params.append(data['password'])

            if 'role' in data:
                update_fields.append('role = ?')
                params.append(data['role'])

            if update_fields:
                params.append(user_id)
                query = f"UPDATE users SET {', '.join(update_fields)} WHERE id = ?"
                cursor.execute(query, params)
                success = cursor.rowcount > 0
                conn.commit()
                return success

            return False
        except Exception as e:
            print(f"❌ 更新用户失败: {e}")
            return False
        finally:
            conn.close()

    def delete_user(self, user_id):
        """删除用户 - 增强保护逻辑"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # 获取要删除的用户信息
            cursor.execute('SELECT username, role FROM users WHERE id = ?', (user_id,))
            user_info = cursor.fetchone()

            if not user_info:
                print(f"❌ 用户不存在: {user_id}")
                return False

            username, role = user_info

            # 防止删除管理员用户
            if role == 'admin':
                print(f"🛡️ 不能删除管理员用户: {username}")
                return False

            cursor.execute('DELETE FROM users WHERE id = ?', (user_id,))
            success = cursor.rowcount > 0
            conn.commit()

            if success:
                print(f"✅ 用户删除成功: {username}")
            else:
                print(f"❌ 用户删除失败: {username}")

            return success
        except Exception as e:
            print(f"❌ 删除用户失败: {e}")
            return False
        finally:
            conn.close()

    # ==================== 试题管理方法 ====================
    def create_question(self, question):
        """创建试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('''
                INSERT INTO questions (
                    id, content, question_type, difficulty, subject, chapter,
                    options, answer, explanation, tags, usage_count, creator_id, created_time
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                question.id, question.content, question.question_type.value,
                question.difficulty.value, question.subject, question.chapter,
                json.dumps(question.options, ensure_ascii=False), question.answer, question.explanation,
                json.dumps(question.tags, ensure_ascii=False), question.usage_count, question.creator_id,
                question.created_time.isoformat()
            ))

            conn.commit()
            print(f"✅ 试题创建成功: {question.id}")
            return True
        except Exception as e:
            print(f"❌ 创建试题失败: {e}")
            return False
        finally:
            conn.close()

    def get_question_by_id(self, question_id):
        """根据ID获取试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT * FROM questions WHERE id = ?', (question_id,))
            row = cursor.fetchone()

            if row:
                question = Question(
                    id=row[0],
                    content=row[1],
                    question_type=QuestionType(row[2]),
                    difficulty=DifficultyLevel(row[3]),
                    subject=row[4],
                    chapter=row[5] or '',
                    options=json.loads(row[6]) if row[6] else [],
                    answer=row[7] or '',
                    explanation=row[8] or '',
                    tags=json.loads(row[9]) if row[9] else [],
                    usage_count=row[10] or 0,
                    creator_id=row[11],
                    created_time=datetime.fromisoformat(row[12])
                )
                return question
            return None
        except Exception as e:
            print(f"❌ 获取试题失败: {e}")
            return None
        finally:
            conn.close()

    def search_questions(self, filters=None):
        """搜索试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            query = "SELECT * FROM questions WHERE 1=1"
            params = []

            if filters:
                if filters.get('subject'):
                    query += " AND subject = ?"
                    params.append(filters['subject'])

                if filters.get('difficulty'):
                    query += " AND difficulty = ?"
                    params.append(filters['difficulty'])

                if filters.get('question_type'):
                    query += " AND question_type = ?"
                    params.append(filters['question_type'])

                if filters.get('creator_id'):
                    query += " AND creator_id = ?"
                    params.append(filters['creator_id'])

                if filters.get('keyword'):
                    query += " AND (content LIKE ? OR answer LIKE ? OR explanation LIKE ?)"
                    keyword = f"%{filters['keyword']}%"
                    params.extend([keyword, keyword, keyword])

            query += " ORDER BY created_time DESC"

            cursor.execute(query, params)
            rows = cursor.fetchall()

            questions = []
            for row in rows:
                try:
                    question = Question(
                        id=row[0],
                        content=row[1],
                        question_type=QuestionType(row[2]),
                        difficulty=DifficultyLevel(row[3]),
                        subject=row[4],
                        chapter=row[5] or '',
                        options=json.loads(row[6]) if row[6] else [],
                        answer=row[7] or '',
                        explanation=row[8] or '',
                        tags=json.loads(row[9]) if row[9] else [],
                        usage_count=row[10] or 0,
                        creator_id=row[11],
                        created_time=datetime.fromisoformat(row[12])
                    )
                    questions.append(question)
                except Exception as e:
                    print(f"❌ 解析试题数据失败: {e}")
                    continue

            print(f"🔍 搜索到 {len(questions)} 个试题")
            return questions
        except Exception as e:
            print(f"❌ 搜索试题失败: {e}")
            return []
        finally:
            conn.close()

    def update_question(self, question_id, data):
        """更新试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('''
                UPDATE questions SET
                    content = ?, question_type = ?, difficulty = ?,
                    subject = ?, chapter = ?, answer = ?, explanation = ?
                WHERE id = ?
            ''', (
                data['content'], data['question_type'], data['difficulty'],
                data['subject'], data.get('chapter', ''),
                data.get('answer', ''), data.get('explanation', ''),
                question_id
            ))

            success = cursor.rowcount > 0
            conn.commit()

            if success:
                print(f"✅ 试题更新成功: {question_id}")
            else:
                print(f"❌ 试题不存在: {question_id}")

            return success
        except Exception as e:
            print(f"❌ 更新试题失败: {e}")
            return False
        finally:
            conn.close()

    def delete_question(self, question_id):
        """删除试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('DELETE FROM questions WHERE id = ?', (question_id,))
            success = cursor.rowcount > 0
            conn.commit()

            if success:
                print(f"✅ 试题删除成功: {question_id}")
            else:
                print(f"❌ 试题不存在: {question_id}")

            return success
        except Exception as e:
            print(f"❌ 删除试题失败: {e}")
            return False
        finally:
            conn.close()

    def increment_question_usage(self, question_id):
        """增加试题使用次数"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('UPDATE questions SET usage_count = usage_count + 1 WHERE id = ?', (question_id,))
            conn.commit()
            return True
        except Exception as e:
            print(f"❌ 更新试题使用次数失败: {e}")
            return False
        finally:
            conn.close()

    def get_all_subjects(self):
        """获取所有学科"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute(
                'SELECT DISTINCT subject FROM questions WHERE subject IS NOT NULL AND subject != "" ORDER BY subject')
            rows = cursor.fetchall()
            subjects = [row[0] for row in rows if row[0] and row[0].strip()]

            # 如果没有学科数据，返回默认学科
            if not subjects:
                subjects = ['数学', '语文', '英语', '物理', '化学', '生物', '历史', '地理', '政治', '计算机']

            return subjects
        except Exception as e:
            print(f"❌ 获取学科列表失败: {e}")
            return ['数学', '语文', '英语', '物理', '化学', '生物', '历史', '地理', '政治', '计算机']
        finally:
            conn.close()

    # ==================== 试卷管理方法 ====================
    def create_paper(self, paper):
        """创建试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('''
                INSERT INTO papers (
                    id, title, description, subject, questions, 
                    total_score, creator_id, created_time
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                paper.id, paper.title, paper.description, paper.subject,
                json.dumps(paper.questions, ensure_ascii=False), paper.total_score,
                paper.creator_id, paper.created_time.isoformat()
            ))

            conn.commit()
            print(f"✅ 试卷创建成功: {paper.title}")
            return True
        except Exception as e:
            print(f"❌ 创建试卷失败: {e}")
            return False
        finally:
            conn.close()

    def get_paper_by_id(self, paper_id):
        """根据ID获取试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT * FROM papers WHERE id = ?', (paper_id,))
            row = cursor.fetchone()

            if row:
                paper = Paper(
                    id=row[0],
                    title=row[1],
                    description=row[2] or '',
                    subject=row[3],
                    questions=json.loads(row[4]) if row[4] else [],
                    total_score=row[5] or 0,
                    creator_id=row[6],
                    created_time=datetime.fromisoformat(row[7]) if row[7] else datetime.now()
                )
                return paper
            return None
        except Exception as e:
            print(f"❌ 获取试卷失败: {e}")
            return None
        finally:
            conn.close()

    def get_all_papers(self):
        """获取所有试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT * FROM papers ORDER BY created_time DESC')
            rows = cursor.fetchall()

            papers = []
            for row in rows:
                try:
                    paper = Paper(
                        id=row[0],
                        title=row[1],
                        description=row[2] or '',
                        subject=row[3],
                        questions=json.loads(row[4]) if row[4] else [],
                        total_score=row[5] or 0,
                        creator_id=row[6],
                        created_time=datetime.fromisoformat(row[7]) if row[7] else datetime.now()
                    )
                    papers.append(paper)
                except Exception as e:
                    print(f"❌ 解析试卷数据失败: {e}")
                    continue

            print(f"📄 获取到 {len(papers)} 个试卷")
            return papers
        except Exception as e:
            print(f"❌ 获取试卷列表失败: {e}")
            return []
        finally:
            conn.close()

    def search_papers(self, filters=None):
        """搜索试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            query = "SELECT * FROM papers WHERE 1=1"
            params = []

            if filters:
                if filters.get('subject'):
                    query += " AND subject = ?"
                    params.append(filters['subject'])

                if filters.get('creator_id'):
                    query += " AND creator_id = ?"
                    params.append(filters['creator_id'])

                if filters.get('keyword'):
                    query += " AND (title LIKE ? OR description LIKE ?)"
                    keyword = f"%{filters['keyword']}%"
                    params.extend([keyword, keyword])

            query += " ORDER BY created_time DESC"

            cursor.execute(query, params)
            rows = cursor.fetchall()

            papers = []
            for row in rows:
                try:
                    paper = Paper(
                        id=row[0],
                        title=row[1],
                        description=row[2] or '',
                        subject=row[3],
                        questions=json.loads(row[4]) if row[4] else [],
                        total_score=row[5] or 0,
                        creator_id=row[6],
                        created_time=datetime.fromisoformat(row[7]) if row[7] else datetime.now()
                    )
                    papers.append(paper)
                except Exception as e:
                    print(f"❌ 解析试卷数据失败: {e}")
                    continue

            return papers
        except Exception as e:
            print(f"❌ 搜索试卷失败: {e}")
            return []
        finally:
            conn.close()

    def update_paper(self, paper_id, data):
        """更新试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('''
                UPDATE papers SET
                    title = ?, description = ?, subject = ?, 
                    questions = ?, total_score = ?
                WHERE id = ?
            ''', (
                data['title'], data.get('description', ''), data['subject'],
                json.dumps(data['questions'], ensure_ascii=False), data.get('total_score', 0),
                paper_id
            ))

            success = cursor.rowcount > 0
            conn.commit()
            return success
        except Exception as e:
            print(f"❌ 更新试卷失败: {e}")
            return False
        finally:
            conn.close()

    def delete_paper(self, paper_id):
        """删除试卷"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('DELETE FROM papers WHERE id = ?', (paper_id,))
            success = cursor.rowcount > 0
            conn.commit()
            return success
        except Exception as e:
            print(f"❌ 删除试卷失败: {e}")
            return False
        finally:
            conn.close()

    # ==================== 统计分析方法 ====================
    def get_statistics(self):
        """获取统计数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # 统计试题总数
            cursor.execute('SELECT COUNT(*) FROM questions')
            total_questions = cursor.fetchone()[0]

            # 统计试卷总数
            cursor.execute('SELECT COUNT(*) FROM papers')
            total_papers = cursor.fetchone()[0]

            # 统计用户总数
            cursor.execute('SELECT COUNT(*) FROM users')
            total_users = cursor.fetchone()[0]

            # 按学科统计试题
            cursor.execute('SELECT subject, COUNT(*) FROM questions GROUP BY subject ORDER BY COUNT(*) DESC')
            subjects_data = cursor.fetchall()
            questions_by_subject = {subject: count for subject, count in subjects_data if subject}

            # 按难度统计试题
            cursor.execute('SELECT difficulty, COUNT(*) FROM questions GROUP BY difficulty')
            difficulty_data = cursor.fetchall()
            difficulty_map = {1: 'easy', 2: 'medium', 3: 'hard', 4: 'very_hard'}
            difficulty_distribution = {
                'easy': 0, 'medium': 0, 'hard': 0, 'very_hard': 0
            }
            for diff, count in difficulty_data:
                difficulty_distribution[difficulty_map.get(diff, 'unknown')] = count

            # 按试题类型统计
            cursor.execute('SELECT question_type, COUNT(*) FROM questions GROUP BY question_type')
            type_data = cursor.fetchall()
            questions_by_type = {qtype: count for qtype, count in type_data}

            # 最活跃的用户（创建最多试题）
            cursor.execute('''
                SELECT u.username, COUNT(q.id) as question_count 
                FROM users u LEFT JOIN questions q ON u.id = q.creator_id 
                GROUP BY u.id 
                ORDER BY question_count DESC 
                LIMIT 5
            ''')
            active_users = cursor.fetchall()

            # 最热门的学科
            cursor.execute('''
                SELECT subject, SUM(usage_count) as total_usage 
                FROM questions 
                WHERE subject IS NOT NULL 
                GROUP BY subject 
                ORDER BY total_usage DESC 
                LIMIT 5
            ''')
            popular_subjects = cursor.fetchall()

            stats = {
                'total_questions': total_questions,
                'total_papers': total_papers,
                'total_users': total_users,
                'questions_by_subject': questions_by_subject,
                'difficulty_distribution': difficulty_distribution,
                'questions_by_type': questions_by_type,
                'active_users': [{'username': u[0], 'question_count': u[1]} for u in active_users],
                'popular_subjects': [{'subject': s[0], 'usage_count': s[1]} for s in popular_subjects]
            }

            print(f"📊 统计完成: {total_questions}题, {total_papers}卷, {total_users}用户")
            return stats

        except Exception as e:
            print(f"❌ 获取统计数据失败: {e}")
            return {
                'total_questions': 0,
                'total_papers': 0,
                'total_users': 0,
                'questions_by_subject': {},
                'difficulty_distribution': {'easy': 0, 'medium': 0, 'hard': 0, 'very_hard': 0},
                'questions_by_type': {},
                'active_users': [],
                'popular_subjects': []
            }
        finally:
            conn.close()

    def get_user_statistics(self, user_id):
        """获取用户个人统计"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # 用户创建的试题数
            cursor.execute('SELECT COUNT(*) FROM questions WHERE creator_id = ?', (user_id,))
            created_questions = cursor.fetchone()[0]

            # 用户创建的试卷数
            cursor.execute('SELECT COUNT(*) FROM papers WHERE creator_id = ?', (user_id,))
            created_papers = cursor.fetchone()[0]

            # 用户试题的总使用次数
            cursor.execute('SELECT SUM(usage_count) FROM questions WHERE creator_id = ?', (user_id,))
            total_usage = cursor.fetchone()[0] or 0

            return {
                'created_questions': created_questions,
                'created_papers': created_papers,
                'total_usage': total_usage
            }
        except Exception as e:
            print(f"❌ 获取用户统计失败: {e}")
            return {'created_questions': 0, 'created_papers': 0, 'total_usage': 0}
        finally:
            conn.close()

    # ==================== 相似度和难度分析方法 ====================
    def get_question_by_content_similarity(self, content, threshold=0.3):
        """根据内容相似度查找试题"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('SELECT id, content, subject FROM questions')
            rows = cursor.fetchall()

            similar_questions = []
            content_words = set(content.lower().split())

            for row in rows:
                existing_content = row[1].lower()
                existing_words = set(existing_content.split())

                # 计算Jaccard相似度
                intersection = len(content_words & existing_words)
                union = len(content_words | existing_words)

                if union > 0:
                    similarity = intersection / union

                    if similarity > threshold:
                        similar_questions.append({
                            'id': row[0],
                            'content': row[1],
                            'subject': row[2],
                            'similarity': similarity
                        })

            # 按相似度降序排列
            similar_questions.sort(key=lambda x: x['similarity'], reverse=True)
            return similar_questions[:5]  # 返回前5个最相似的

        except Exception as e:
            print(f"❌ 相似度检查失败: {e}")
            return []
        finally:
            conn.close()

    def save_similarity_result(self, question_id, similar_question_id, similarity_score):
        """保存相似度检查结果"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            result_id = str(uuid.uuid4())
            cursor.execute('''
                INSERT INTO similarity_results (id, question_id, similar_question_id, similarity_score, created_time)
                VALUES (?, ?, ?, ?, ?)
            ''', (result_id, question_id, similar_question_id, similarity_score, datetime.now().isoformat()))

            conn.commit()
            return True
        except Exception as e:
            print(f"❌ 保存相似度结果失败: {e}")
            return False
        finally:
            conn.close()

    def save_difficulty_analysis(self, question_id, predicted_difficulty, actual_difficulty, confidence):
        """保存难度分析结果"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            analysis_id = str(uuid.uuid4())
            cursor.execute('''
                INSERT INTO difficulty_analysis (id, question_id, predicted_difficulty, actual_difficulty, confidence, created_time)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (
            analysis_id, question_id, predicted_difficulty, actual_difficulty, confidence, datetime.now().isoformat()))

            conn.commit()
            return True
        except Exception as e:
            print(f"❌ 保存难度分析失败: {e}")
            return False
        finally:
            conn.close()

    # ==================== 日志记录方法 ====================
    def create_log(self, user_id, action, description, ip_address=None):
        """创建操作日志"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cursor.execute('''
                INSERT INTO logs (user_id, action, description, ip_address, timestamp)
                VALUES (?, ?, ?, ?, ?)
            ''', (user_id, action, description, ip_address, datetime.now().isoformat()))

            conn.commit()
            return True
        except Exception as e:
            print(f"❌ 创建日志失败: {e}")
            return False
        finally:
            conn.close()

    def get_logs(self, limit=100, user_id=None):
        """获取操作日志"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            if user_id:
                cursor.execute('''
                    SELECT l.*, u.username 
                    FROM logs l 
                    LEFT JOIN users u ON l.user_id = u.id 
                    WHERE l.user_id = ?
                    ORDER BY l.timestamp DESC 
                    LIMIT ?
                ''', (user_id, limit))
            else:
                cursor.execute('''
                    SELECT l.*, u.username 
                    FROM logs l 
                    LEFT JOIN users u ON l.user_id = u.id 
                    ORDER BY l.timestamp DESC 
                    LIMIT ?
                ''', (limit,))

            rows = cursor.fetchall()
            logs = []

            for row in rows:
                logs.append({
                    'id': row[0],
                    'user_id': row[1],
                    'action': row[2],
                    'description': row[3],
                    'ip_address': row[4],
                    'timestamp': row[5],
                    'username': row[6] or 'Unknown'
                })

            return logs
        except Exception as e:
            print(f"❌ 获取日志失败: {e}")
            return []
        finally:
            conn.close()

    # ==================== 数据清理和维护方法 ====================
    def cleanup_old_logs(self, days=30):
        """清理超过指定天数的日志"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            cutoff_date = datetime.now() - timedelta(days=days)
            cursor.execute('DELETE FROM logs WHERE timestamp < ?', (cutoff_date.isoformat(),))
            deleted_count = cursor.rowcount
            conn.commit()

            print(f"🧹 清理了 {deleted_count} 条旧日志")
            return deleted_count
        except Exception as e:
            print(f"❌ 清理日志失败: {e}")
            return 0
        finally:
            conn.close()

    def vacuum_database(self):
        """优化数据库"""
        conn = sqlite3.connect(self.db_path)

        try:
            conn.execute('VACUUM')
            print("🔧 数据库优化完成")
            return True
        except Exception as e:
            print(f"❌ 数据库优化失败: {e}")
            return False
        finally:
            conn.close()

    def get_database_info(self):
        """获取数据库信息"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()

        try:
            # 获取表信息
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table'")
            tables = [row[0] for row in cursor.fetchall()]

            table_info = {}
            for table in tables:
                cursor.execute(f"SELECT COUNT(*) FROM {table}")
                count = cursor.fetchone()[0]
                table_info[table] = count

            # 获取数据库文件大小
            import os
            file_size = os.path.getsize(self.db_path) if os.path.exists(self.db_path) else 0

            return {
                'tables': table_info,
                'file_size': file_size,
                'file_path': self.db_path
            }
        except Exception as e:
            print(f"❌ 获取数据库信息失败: {e}")
            return {}
        finally:
            conn.close()

    def save_similarity_result(self, question_id: str, similar_question_id: str,
                               similarity_score: float, algorithm: str = 'jaccard'):
        """保存相似度检查结果"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            result_id = str(uuid.uuid4())
            cursor.execute('''
                INSERT INTO similarity_results 
                (id, question_id, similar_question_id, similarity_score, algorithm_used, created_time)
                VALUES (?, ?, ?, ?, ?, ?)
            ''', (result_id, question_id, similar_question_id, similarity_score,
                  algorithm, datetime.now().isoformat()))
            conn.commit()
            return True
        except Exception as e:
            print(f"❌保存相似度结果失败: {e}")
            return False
        finally:
            conn.close()

    def get_similarity_history(self, question_id: str) -> List[Dict]:
        """获取试题的相似度检查历史"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            cursor.execute('''
                SELECT sr.*, q.content, q.subject 
                FROM similarity_results sr
                JOIN questions q ON sr.similar_question_id = q.id
                WHERE sr.question_id = ?
                ORDER BY sr.similarity_score DESC
            ''', (question_id,))

            results = []
            for row in cursor.fetchall():
                results.append({
                    'id': row[0],
                    'similar_question_id': row[2],
                    'similarity_score': row[3],
                    'algorithm_used': row[4],
                    'created_time': row[5],
                    'similar_content': row[6],
                    'similar_subject': row[7]
                })
            return results
        except Exception as e:
            print(f"❌获取相似度历史失败: {e}")
            return []
        finally:
            conn.close()

    # ==================== 新增：难度分析功能 ====================

    def save_difficulty_analysis(self, question_id: str, predicted_difficulty: int,
                                 actual_difficulty: int, confidence: float,
                                 factors: Dict):
        """保存难度分析结果"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            analysis_id = str(uuid.uuid4())
            cursor.execute('''
                INSERT INTO difficulty_analysis 
                (id, question_id, predicted_difficulty, actual_difficulty, 
                 confidence, analysis_factors, created_time)
                VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (analysis_id, question_id, predicted_difficulty, actual_difficulty,
                  confidence, json.dumps(factors, ensure_ascii=False),
                  datetime.now().isoformat()))
            conn.commit()
            return True
        except Exception as e:
            print(f"❌保存难度分析失败: {e}")
            return False
        finally:
            conn.close()

    def get_paper_difficulty_analysis(self, paper_id: str) -> Dict:
        """分析试卷难度分布"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            # 获取试卷中的试题
            cursor.execute('SELECT questions FROM papers WHERE id = ?', (paper_id,))
            paper_row = cursor.fetchone()
            if not paper_row:
                return {}

            questions = json.loads(paper_row[0])
            question_ids = []
            for q in questions:
                if isinstance(q, dict):
                    question_ids.append(q.get('question_id'))
                else:
                    question_ids.append(q)

            if not question_ids:
                return {}

            # 获取试题难度分布
            placeholders = ','.join(['?' for _ in question_ids])
            cursor.execute(f'''
                SELECT difficulty, COUNT(*) as count, AVG(usage_count) as avg_usage
                FROM questions 
                WHERE id IN ({placeholders})
                GROUP BY difficulty
            ''', question_ids)

            difficulty_dist = {}
            total_questions = 0

            for row in cursor.fetchall():
                difficulty_dist[row[0]] = {
                    'count': row[1],
                    'average_usage': row[2] or 0
                }
                total_questions += row[1]

            # 计算难度系数
            weighted_difficulty = sum(k * v['count'] for k, v in difficulty_dist.items())
            difficulty_coefficient = weighted_difficulty / total_questions if total_questions > 0 else 2

            return {
                'paper_id': paper_id,
                'total_questions': total_questions,
                'difficulty_distribution': difficulty_dist,
                'difficulty_coefficient': difficulty_coefficient
            }
        except Exception as e:
            print(f"❌分析试卷难度失败: {e}")
            return {}
        finally:
            conn.close()

    # ==================== 新增：考察时间查询功能 ====================

    def add_exam_record(self, question_id: str, exam_date: datetime,
                        exam_name: str = "", paper_id: str = "",
                        knowledge_points: List[str] = None):
        """添加考察记录"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            # 获取试题信息
            cursor.execute('SELECT subject, chapter FROM questions WHERE id = ?', (question_id,))
            question_info = cursor.fetchone()
            if not question_info:
                return False

            record_id = str(uuid.uuid4())
            cursor.execute('''
                INSERT INTO exam_records 
                (id, question_id, exam_date, exam_name, paper_id, subject, chapter, 
                 knowledge_points, created_time)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (record_id, question_id, exam_date.isoformat(), exam_name, paper_id,
                  question_info[0], question_info[1] or "",
                  json.dumps(knowledge_points or [], ensure_ascii=False),
                  datetime.now().isoformat()))
            conn.commit()
            return True
        except Exception as e:
            print(f"❌添加考察记录失败: {e}")
            return False
        finally:
            conn.close()

    def get_question_exam_history(self, question_id: str) -> List[Dict]:
        """获取试题考察历史"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            cursor.execute('''
                SELECT * FROM exam_records 
                WHERE question_id = ? 
                ORDER BY exam_date DESC
            ''', (question_id,))

            records = []
            for row in cursor.fetchall():
                records.append({
                    'id': row[0],
                    'exam_date': row[2],
                    'exam_name': row[3],
                    'paper_id': row[4],
                    'subject': row[5],
                    'chapter': row[6],
                    'knowledge_points': json.loads(row[7]) if row[7] else []
                })
            return records
        except Exception as e:
            print(f"❌获取考察历史失败: {e}")
            return []
        finally:
            conn.close()

    def get_exam_frequency_stats(self, start_date: datetime = None,
                                 end_date: datetime = None,
                                 subject: str = None) -> Dict:
        """获取考察频次统计"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            query = '''
                SELECT 
                    q.id, q.content, q.subject, q.chapter,
                    COUNT(er.id) as exam_count,
                    MAX(er.exam_date) as last_exam_date
                FROM questions q
                LEFT JOIN exam_records er ON q.id = er.question_id
                WHERE 1=1
            '''
            params = []

            if start_date:
                query += ' AND er.exam_date >= ?'
                params.append(start_date.isoformat())
            if end_date:
                query += ' AND er.exam_date <= ?'
                params.append(end_date.isoformat())
            if subject:
                query += ' AND q.subject = ?'
                params.append(subject)

            query += ' GROUP BY q.id ORDER BY exam_count DESC'

            cursor.execute(query, params)

            frequency_stats = []
            for row in cursor.fetchall():
                frequency_stats.append({
                    'question_id': row[0],
                    'content': row[1][:100] + '...' if len(row[1]) > 100 else row[1],
                    'subject': row[2],
                    'chapter': row[3] or "",
                    'exam_count': row[4],
                    'last_exam_date': row[5]
                })

            return {
                'frequency_stats': frequency_stats,
                'total_questions': len(frequency_stats),
                'total_exams': sum(item['exam_count'] for item in frequency_stats)
            }
        except Exception as e:
            print(f"❌获取考察频次统计失败: {e}")
            return {}
        finally:
            conn.close()

    # ==================== 新增：增强的数据统计功能 ====================

    def get_comprehensive_statistics(self) -> Dict:
        """获取综合统计数据"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        try:
            stats = {}

            # 基础统计
            cursor.execute('SELECT COUNT(*) FROM questions')
            stats['total_questions'] = cursor.fetchone()[0]

            cursor.execute('SELECT COUNT(*) FROM papers')
            stats['total_papers'] = cursor.fetchone()[0]

            cursor.execute('SELECT COUNT(*) FROM users')
            stats['total_users'] = cursor.fetchone()[0]

            cursor.execute('SELECT COUNT(*) FROM exam_records')
            stats['total_exams'] = cursor.fetchone()[0]

            # 学科分布
            cursor.execute('''
                SELECT subject, COUNT(*) as count, SUM(usage_count) as total_usage
                FROM questions 
                GROUP BY subject 
                ORDER BY count DESC
            ''')
            stats['subject_distribution'] = [
                {'subject': row[0], 'count': row[1], 'total_usage': row[2] or 0}
                for row in cursor.fetchall()
            ]

            # 难度分布
            cursor.execute('''
                SELECT difficulty, COUNT(*) as count
                FROM questions 
                GROUP BY difficulty
            ''')
            difficulty_dist = {}
            for row in cursor.fetchall():
                difficulty_dist[str(row[0])] = row[1]
            stats['difficulty_distribution'] = difficulty_dist

            return stats
        except Exception as e:
            print(f"❌获取综合统计失败: {e}")
            return {}
        finally:
            conn.close()